/*
 * (c) 2013 
 * 
 * Project       : Presensi
 * Filename      : DBAdapter.java
 * Creation Date : Mar 24, 2013 time : 11:50:03 AM
 *
 */

package id.khairizalnetani.tugasakhir;

import java.util.ArrayList;
import java.util.List;
import id.khairizalnetani.tugasakhir.entity.Keterangan;
import id.khairizalnetani.tugasakhir.entity.Laporan;
import id.khairizalnetani.tugasakhir.entity.Siswa;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

/*
 * Class untuk menangani Database
 * meliputi insert, update, delete 
 * 
 * CRUD (Create, Read, Update, Delete)
 */
public class DBAdapter
{

	private static final String	DB_NAME							= "presensi";
	private static final int	DB_VER							= 1;

	private DatabaseHelper		dbHelper;
	private SQLiteDatabase		db;
	private final Context		context;

	private final String		TB_SISWA						= "wing_korps_karbol";
	private final String		COL_SISWA_ID					= "_id";
	private final String		COL_SISWA_NAME					= "nama";
	private final String		COL_SISWA_KODE					= "kode_aktivasi";
	private final String		COL_SISWA_FLIGHT				= "flight";
	private final String		COL_SISWA_SKADRON				= "skadron";

	private final String		TB_REPORT						= "laporan";
	private final String		COL_REPORT_ID					= "_id";
	private final String		COL_REPORT_ID_PELAPOR			= "id_pelapor";
	private final String		COL_REPORT_JENIS_APEL			= "jenis_apel";
	private final String		COL_REPORT_TANGGAL				= "tanggal";
	private final String		COL_REPORT_JUMLAH				= "jumlah";
	private final String		COL_REPORT_KURANG				= "kurang";
	private final String		COL_REPORT_HADIR				= "hadir";

	private final String		TB_KETERANGAN					= "tb_keterangan";
	private final String		COL_KETERANGAN_ID				= "_id";
	private final String		COL_KETERANGAN_ID_REPORT		= "id_report";
	private final String		COL_KETERANGAN_ID_SISWA			= "id_siswa";
	private final String		COL_KETERANGAN_JENIS_KETERANGAN	= "jenis_keterangan";
	private final String		COL_KETERANGAN_RUMAHSAKIT		= "rumahsakit";

	private static final int	TXT_DINASDALAM					= 0;
	private static final int	TXT_DINASLUAR					= 1;
	private static final int	TXT_SAKIT						= 2;
	private static final int	TXT_BEROBAT						= 3;
	private static final int	TXT_IZIN						= 4;
	private static final int	TXT_TANPAKETERANGAN				= 5;

	// inner class
	private static class DatabaseHelper extends SQLiteAssetHelper
	{
		public DatabaseHelper(Context context)
		{
			super(context, DB_NAME, null, DB_VER);
		}
	}

	// end inner class

	public DBAdapter(Context context)
	{
		this.context = context;
	}

	// open database
	public DBAdapter open() throws SQLException
	{
		dbHelper = new DatabaseHelper(context);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	// closing database
	public void close()
	{
		dbHelper.close();
	}

	// ini fungsi untuk check id
	public boolean checkId(int id, int flight, int skadron)
	{

		Cursor cursor = db.query(TB_SISWA, new String[]
		{
				COL_SISWA_ID, COL_SISWA_NAME, COL_SISWA_KODE, COL_SISWA_FLIGHT, COL_SISWA_SKADRON
		}, COL_SISWA_ID + "=" + id + " AND " + COL_SISWA_FLIGHT + "=" + flight + " AND " + COL_SISWA_SKADRON + "="
				+ skadron, null, null, null, null);

		if (cursor.getCount() == 1)
			return true;
		else
			return false;
	}

	// fungsi untuk check kode konfirmasi
	public boolean checkKodeKonfirmasi(int id, String kode)
	{
		Cursor cursor = db.query(TB_SISWA, new String[]
		{
				COL_SISWA_ID, COL_SISWA_NAME, COL_SISWA_KODE
		}, COL_SISWA_ID + "=" + id + " AND " + COL_SISWA_KODE + "='" + kode + "'", null, null, null, null);

		if (cursor.getCount() == 1)
			return true;
		else
			return false;
	}

	// fungsi untuk membuat laporan
	public long createReport(Laporan report)
	{
		ContentValues val = new ContentValues();

		val.put(COL_REPORT_ID_PELAPOR, report.getId_pelapor());
		val.put(COL_REPORT_JENIS_APEL, report.getJenis_apel());
		val.put(COL_REPORT_TANGGAL, report.getTanggal());
		val.put(COL_REPORT_JUMLAH, report.getJumlah());
		val.put(COL_REPORT_KURANG, report.getKurang());
		val.put(COL_REPORT_HADIR, report.getHadir());
		Utils.TRACE("create new report");
		// sql insert ke database
		return db.insert(TB_REPORT, null, val);

	}

	// fungsi untuk insert laporan berupa keterangan selain sakit dan berobat
	public void createReportKet(Keterangan keterangan)
	{
		ContentValues values = new ContentValues();

		values.put(COL_KETERANGAN_ID_REPORT, keterangan.getId_report());
		values.put(COL_KETERANGAN_ID_SISWA, keterangan.getId_siswa());
		values.put(COL_KETERANGAN_JENIS_KETERANGAN, keterangan.getJenis_keterangan());

		db.insert(TB_KETERANGAN, null, values);
	}

	// fungsi untuk insert laporan berupa keterangan sakit atau berobat
	public void createReportSakit(Keterangan keterangan)
	{
		ContentValues values = new ContentValues();
		values.put(COL_KETERANGAN_ID_REPORT, keterangan.getId_report());
		values.put(COL_KETERANGAN_ID_SISWA, keterangan.getId_siswa());
		values.put(COL_KETERANGAN_JENIS_KETERANGAN, keterangan.getJenis_keterangan());
		values.put(COL_KETERANGAN_RUMAHSAKIT, keterangan.getRumahsakit());

		db.insert(TB_KETERANGAN, null, values);
	}

	// fungsi untuk delete keterangan by id report
	// mengembalikan nilai true jika terdapat baris yang didelete
	public boolean deleteKeteranganByReport(long idReport)
	{
		return db.delete(TB_KETERANGAN, COL_KETERANGAN_ID_REPORT + "=" + idReport, null) > 0;
	}

	// fungsi untuk update laporan
	public boolean updateLaporan(Laporan laporan)
	{
		ContentValues val = new ContentValues();

		val.put(COL_REPORT_ID, laporan.getId_report());
		val.put(COL_REPORT_ID_PELAPOR, laporan.getId_pelapor());
		val.put(COL_REPORT_JENIS_APEL, laporan.getJenis_apel());
		val.put(COL_REPORT_TANGGAL, laporan.getTanggal());
		val.put(COL_REPORT_JUMLAH, laporan.getJumlah());
		val.put(COL_REPORT_KURANG, laporan.getKurang());
		val.put(COL_REPORT_HADIR, laporan.getHadir());

		return db.update(TB_REPORT, val, COL_REPORT_ID + "=" + laporan.getId_report(), null) > 0;
	}

	// fungsi untuk mendapatkan jumlah anggota kelas dan tingkat berdasarkan
	// flight dan skadron
	public int getCountByFlight(int flight, int skadron)
	{
		Cursor cursor = db.query(TB_SISWA, new String[]
		{
				COL_SISWA_ID, COL_SISWA_NAME, COL_SISWA_FLIGHT
		}, COL_SISWA_FLIGHT + "=" + flight + " AND " + COL_SISWA_SKADRON + "=" + skadron, null, null, null, null);
		Utils.TRACE("" + cursor.getCount());
		return cursor.getCount();
	}

	// fungsi untuk mendapatkan semua siswa berdasarkan flight dan skadron dan
	// bukan ketua kelas
	public ArrayList<Siswa> getAllSiswaBySkadronAndFlight(int flight, int skadron, int siswaID)
	{
		ArrayList<Siswa> listSiswa = new ArrayList<Siswa>();

		Cursor cursor = db.query(TB_SISWA, new String[]
		{
				COL_SISWA_ID, COL_SISWA_NAME
		}, COL_SISWA_FLIGHT + "=" + flight + " AND " + COL_SISWA_SKADRON + "=" + skadron + " AND " + COL_SISWA_ID
				+ "<>" + siswaID, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			Siswa siswa = new Siswa(cursor.getInt(cursor.getColumnIndexOrThrow(COL_SISWA_ID)), cursor.getString(cursor
					.getColumnIndexOrThrow(COL_SISWA_NAME)));
			Utils.TRACE("get siswa " + cursor.getString(cursor.getColumnIndexOrThrow(COL_SISWA_NAME)));
			listSiswa.add(siswa);
			cursor.moveToNext();
		}

		return listSiswa;
	}

	// fungsi untuk mendapatkan siswa by id
	public Siswa getSiswaById(int id)
	{
		Cursor cursor = db.query(TB_SISWA, new String[]
		{
				COL_SISWA_ID, COL_SISWA_NAME, COL_SISWA_KODE, COL_SISWA_FLIGHT, COL_SISWA_SKADRON
		}, COL_SISWA_ID + "=" + id, null, null, null, null);
		if (cursor != null)
		{
			cursor.moveToFirst();
		}
		Siswa siswa = new Siswa();
		siswa.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COL_SISWA_ID)));
		siswa.setNama(cursor.getString(cursor.getColumnIndexOrThrow(COL_SISWA_NAME)));
		siswa.setKode_aktivasi(cursor.getString(cursor.getColumnIndexOrThrow(COL_SISWA_KODE)));
		siswa.setFlight(cursor.getInt(cursor.getColumnIndexOrThrow(COL_SISWA_FLIGHT)));
		siswa.setSkadron(cursor.getInt(cursor.getColumnIndexOrThrow(COL_SISWA_SKADRON)));

		return siswa;
	}

	// fungsi untuk mengambil report untuk ditampilkan di halaman laporan
	public List<Laporan> getAllReport()
	{
		List<Laporan> listLaporan = new ArrayList<Laporan>();
		Cursor cursor = db.query(TB_REPORT, new String[]
		{
				COL_REPORT_ID, COL_REPORT_ID_PELAPOR, COL_REPORT_JENIS_APEL, COL_REPORT_TANGGAL
		}, null, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			Laporan laporan = new Laporan();
			laporan.setId_report(cursor.getLong(cursor.getColumnIndexOrThrow(COL_REPORT_ID)));
			laporan.setId_pelapor(cursor.getInt(cursor.getColumnIndexOrThrow(COL_REPORT_ID_PELAPOR)));
			laporan.setJenis_apel(cursor.getInt(cursor.getColumnIndexOrThrow(COL_REPORT_JENIS_APEL)));
			laporan.setTanggal(cursor.getString(cursor.getColumnIndexOrThrow(COL_REPORT_TANGGAL)));
			Utils.TRACE("add report id " + laporan.getId_report());
			listLaporan.add(laporan);
			cursor.moveToNext();

		}

		return listLaporan;

	}

	// fungsi untuk mendapatkan laporan berdasarkan id
	public Laporan getReportById(long id)
	{

		Cursor cursor = db.query(TB_REPORT, new String[]
		{
				COL_REPORT_ID, COL_REPORT_ID_PELAPOR, COL_REPORT_JENIS_APEL, COL_REPORT_TANGGAL, COL_REPORT_JUMLAH,
				COL_REPORT_KURANG, COL_REPORT_HADIR
		}, COL_REPORT_ID + "=" + id, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		Laporan laporan = new Laporan();

		laporan.setId_report(cursor.getLong(cursor.getColumnIndexOrThrow(COL_REPORT_ID)));
		laporan.setId_pelapor(cursor.getInt(cursor.getColumnIndexOrThrow(COL_REPORT_ID_PELAPOR)));
		laporan.setJenis_apel(cursor.getInt(cursor.getColumnIndexOrThrow(COL_REPORT_JENIS_APEL)));
		laporan.setTanggal(cursor.getString(cursor.getColumnIndexOrThrow(COL_REPORT_TANGGAL)));
		laporan.setJumlah(cursor.getInt(cursor.getColumnIndexOrThrow(COL_REPORT_JUMLAH)));
		laporan.setKurang(cursor.getInt(cursor.getColumnIndexOrThrow(COL_REPORT_KURANG)));
		laporan.setHadir(cursor.getInt(cursor.getColumnIndexOrThrow(COL_REPORT_HADIR)));

		return laporan;
	}

	// fungsi untuk mendapatkan keterangan berdasarkan id
	public List<Keterangan> getKeteranganByIdReport(long idReport)
	{
		List<Keterangan> listKeterangan = new ArrayList<Keterangan>();

		Cursor cursor = db.query(TB_KETERANGAN, new String[]
		{
				COL_KETERANGAN_ID, COL_KETERANGAN_ID_REPORT, COL_KETERANGAN_ID_SISWA, COL_KETERANGAN_JENIS_KETERANGAN,
				COL_KETERANGAN_RUMAHSAKIT
		}, COL_KETERANGAN_ID_REPORT + "=" + idReport, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			listKeterangan
					.add(new Keterangan(cursor.getLong(cursor.getColumnIndexOrThrow(COL_KETERANGAN_ID_REPORT)), cursor
							.getInt(cursor.getColumnIndexOrThrow(COL_KETERANGAN_ID_SISWA)), cursor.getInt(cursor
							.getColumnIndexOrThrow(COL_KETERANGAN_JENIS_KETERANGAN)),
							cursor.getString(cursor.getColumnIndexOrThrow(COL_KETERANGAN_RUMAHSAKIT))));
			cursor.moveToNext();
		}
		return listKeterangan;

	}

	// untuk menghitung jumlah Keterangan per item
	public int getCountKeteranganByJenisAndIdReport(int jenis, long id_report)
	{
		switch (jenis)
		{
			case TXT_DINASDALAM:
				return getCountDinasDalamByReport(id_report);

			case TXT_DINASLUAR:
				return getCountDinasLuarByReport(id_report);

			case TXT_SAKIT:
				return getCountSakitByReport(id_report);

			case TXT_BEROBAT:
				return getCountBerobatByReport(id_report);

			case TXT_IZIN:
				return getCountIzinByReport(id_report);

			case TXT_TANPAKETERANGAN:
				return getCountTanpaKeteranganByReport(id_report);

		}
		return 0;

	}

	// untuk hitung keterangan TK
	private int getCountTanpaKeteranganByReport(long id_report)
	{
		Cursor cursor = db.query(TB_KETERANGAN, new String[]
		{
				COL_KETERANGAN_ID, COL_KETERANGAN_ID_REPORT, COL_KETERANGAN_JENIS_KETERANGAN
		}, COL_KETERANGAN_ID_REPORT + "=" + id_report + " AND " + COL_KETERANGAN_JENIS_KETERANGAN + "="
				+ TXT_TANPAKETERANGAN, null, null, null, null);
		return cursor.getCount();
	}

	// untuk hitung keterangan Izin
	private int getCountIzinByReport(long id_report)
	{
		Cursor cursor = db.query(TB_KETERANGAN, new String[]
		{
				COL_KETERANGAN_ID, COL_KETERANGAN_ID_REPORT, COL_KETERANGAN_JENIS_KETERANGAN
		}, COL_KETERANGAN_ID_REPORT + "=" + id_report + " AND " + COL_KETERANGAN_JENIS_KETERANGAN + "="
				+ TXT_IZIN, null, null, null, null);
		return cursor.getCount();
	}

	// untuk hitung keterangan berobat
	private int getCountBerobatByReport(long id_report)
	{
		Cursor cursor = db.query(TB_KETERANGAN, new String[]
		{
				COL_KETERANGAN_ID, COL_KETERANGAN_ID_REPORT, COL_KETERANGAN_JENIS_KETERANGAN
		}, COL_KETERANGAN_ID_REPORT + "=" + id_report + " AND " + COL_KETERANGAN_JENIS_KETERANGAN + "="
				+ TXT_BEROBAT, null, null, null, null);
		return cursor.getCount();
	}

	// untuk hitung keterangan Sakit
	private int getCountSakitByReport(long id_report)
	{
		Cursor cursor = db.query(TB_KETERANGAN, new String[]
		{
				COL_KETERANGAN_ID, COL_KETERANGAN_ID_REPORT, COL_KETERANGAN_JENIS_KETERANGAN
		}, COL_KETERANGAN_ID_REPORT + "=" + id_report + " AND " + COL_KETERANGAN_JENIS_KETERANGAN + "="
				+ TXT_SAKIT, null, null, null, null);
		return cursor.getCount();
	}

	// untuk hitung keterangan DL
	private int getCountDinasLuarByReport(long id_report)
	{
		Cursor cursor = db.query(TB_KETERANGAN, new String[]
		{
				COL_KETERANGAN_ID, COL_KETERANGAN_ID_REPORT, COL_KETERANGAN_JENIS_KETERANGAN
		}, COL_KETERANGAN_ID_REPORT + "=" + id_report + " AND " + COL_KETERANGAN_JENIS_KETERANGAN + "="
				+ TXT_DINASLUAR, null, null, null, null);
		return cursor.getCount();
	}

	// untuk hitung keterangan DD
	private int getCountDinasDalamByReport(long id_report)
	{
		Cursor cursor = db.query(TB_KETERANGAN, new String[]
		{
				COL_KETERANGAN_ID, COL_KETERANGAN_ID_REPORT, COL_KETERANGAN_JENIS_KETERANGAN
		}, COL_KETERANGAN_ID_REPORT + "=" + id_report + " AND " + COL_KETERANGAN_JENIS_KETERANGAN + "="
				+ TXT_DINASDALAM, null, null, null, null);
		return cursor.getCount();
	}
}
